﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Common.Utilities;

namespace Workflows.Components.TextMining.Collapse
{
    /// <summary>
    /// 
    /// </summary>
    public class TableFieldValueDistribution
    {
        #region props
        private string _TableName;
        /// <summary></summary>
        public string TableName
        {
            get
            {
                return this._TableName;
            }
            set
            {
                this._TableName = value;
            }
        }

        private string _FieldName;
        /// <summary></summary>
        public string FieldName
        {
            get
            {
                return this._FieldName;
            }
            set
            {
                this._FieldName = value;
            }
        }

        private SqlDbType _FieldDbType;
        /// <summary></summary>
        public SqlDbType FieldDbType
        {
            get
            {
                return this._FieldDbType;
            }
            set
            {
                this._FieldDbType = value;
            }
        }

        private Dictionary<string, int> _Spreads;
        /// <summary></summary>
        public Dictionary<string, int> Spreads
        {
            get
            {
                return this._Spreads;
            }
            set
            {
                this._Spreads = value;
            }
        }

        private List<ModePercentile> _ModePcts;
        /// <summary></summary>
        public List<ModePercentile> ModePcts
        {
            get
            {
                return this._ModePcts;
            }
            set
            {
                this._ModePcts = value;
            }
        }

        private double _EvennessIndex;
        /// <summary></summary>
        public double EvennessIndex
        {
            get
            {
                return this._EvennessIndex;
            }
            set
            {
                this._EvennessIndex = value;
            }
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="fieldName"></param>
        /// <param name="dbType"></param>
        /// <param name="spreads"></param>
        /// <param name="modePcts"></param>
        /// <param name="evennessIndex"></param>
        public TableFieldValueDistribution(string tblName, string fieldName, SqlDbType dbType,
            Dictionary<string,int> spreads, 
            List<ModePercentile> modePcts,
            double evennessIndex)
        {
            this._TableName = tblName;
            this._FieldName = fieldName;
            this._FieldDbType = dbType;
            this._Spreads = spreads;
            this._ModePcts = modePcts;
            this._EvennessIndex = evennessIndex;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="fieldName"></param>
        public TableFieldValueDistribution(string connStr, string tblName, string fieldName)
        {
            this._TableName = tblName;
            this._FieldName = fieldName;

            Dictionary<string, int> spreads = new Dictionary<string, int>();
            List<ModePercentile> modePercentiles = new List<ModePercentile>();
            double evennessIdx = 0;

            SqlConnection conn = new SqlConnection(connStr);
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr, string.Empty,
                                                    tblName, fieldName);
            try
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = string.Format("select count (distinct {1}) from {0} where {1} is not null",
                                                this._TableName,
                                                this._FieldName);
                int distinctCount = int.Parse(cmd.ExecuteScalar().ToString());
                if (distinctCount > 0)
                {
                    List<object> values = new List<object>();
                    cmd.CommandText = string.Format("select {1} from {0} where {1} is not null order by {1} asc",
                                                    this._TableName, this._FieldName);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        values.Add(reader[0]);
                    }
                    reader.Close();
                    object min = values[0];
                    object max = values[values.Count - 1];

                    cmd.CommandText =
                        string.Format(
                            "select {1}, count({1}) as Count from " +
                            "{0} where {1} is not null " +
                            "group by {1} " +
                            "order by count ({1}) asc",
                            this._TableName, this._FieldName);
                    reader = cmd.ExecuteReader();
                    int rankScore = 0;
                    ModePercentile modePct = new ModePercentile();
                    modePct.AverageFrequency = 0;
                    modePct.Count = 0;
                    modePct.DistinctValues = new List<string>();
                    modePct.Percentile = 0;
                    modePct.SD = 0;
                    List<int> countsByDistinctValues = new List<int>();
                    while (reader.Read())
                    {
                        rankScore++;
                        if (modePct.Count > distinctCount / 10)
                        {
                            // calculate SD
                            double sqreSum = 0;
                            foreach (int countByVal in countsByDistinctValues)
                            {
                                sqreSum +=
                                    Math.Pow(countByVal - modePct.AverageFrequency, 2);
                            }
                            modePct.SD = Math.Sqrt(sqreSum / modePct.Count);
                            modePercentiles.Add(modePct);

                            modePct = new ModePercentile();
                            modePct.AverageFrequency = 0;
                            modePct.Count = 0;
                            modePct.DistinctValues = new List<string>();
                            modePct.Percentile = 0;
                            modePct.SD = 0;
                            countsByDistinctValues = new List<int>();
                        }

                        int percentile = rankScore * 100 / distinctCount;
                        string value = reader[this._FieldName].ToString();
                        int count = (int)reader["Count"];
                        if (!modePct.DistinctValues.Contains(value))
                        {
                            modePct.DistinctValues.Add(value);
                        }
                        countsByDistinctValues.Add(count);
                        modePct.Percentile = (int)Math.Floor((double)percentile / 10) * 10;
                        modePct.Count++;
                        modePct.AverageFrequency += (double)count / (distinctCount / 10);
                    }
                    reader.Close();
                    if (modePct.Count > 0)
                    {
                        // calculate SD
                        double sqreSum = 0;
                        foreach (int countByVal in countsByDistinctValues)
                        {
                            sqreSum +=
                                Math.Pow(countByVal - modePct.AverageFrequency, 2);
                        }
                        modePct.SD = Math.Sqrt(sqreSum / modePct.Count);
                        modePercentiles.Add(modePct);
                    }

                    if (modePercentiles.Count > 0)
                    {
                        double totalSum = 0;
                        foreach (ModePercentile modPct in modePercentiles)
                        {
                            totalSum += modPct.AverageFrequency * modPct.Count;
                        }
                        double avgCount = totalSum / distinctCount;
                        double totalSqreDiff = 0;
                        foreach (ModePercentile modPct in modePercentiles)
                        {
                            totalSqreDiff += Math.Pow(modPct.AverageFrequency - avgCount, 2) / avgCount;
                        }
                        evennessIdx = Math.Sqrt(totalSqreDiff / modePercentiles.Count);
                    }

                    if (FieldDataType.IsNumber(col.DbType))
                    {
                        int interval =
                            (int)(double.Parse(max.ToString()) - double.Parse(min.ToString())) /
                            10;
                        int minValue = (int)Math.Floor(double.Parse(min.ToString()));
                        int maxValue = (int)Math.Ceiling(double.Parse(max.ToString()));

                        if (interval >= 1)
                        {
                            int fromValue = minValue;
                            int toValue = fromValue + interval;
                            while (toValue < maxValue)
                            {
                                int count = 0;
                                foreach (object item in values)
                                {
                                    double value = double.Parse(item.ToString());
                                    if (value >= fromValue && value < toValue)
                                    {
                                        count++;
                                    }
                                    if (value > toValue)
                                    {
                                        break;
                                    }
                                }

                                spreads.Add(string.Format("{0}-{1}", fromValue, toValue), count);
                                fromValue = toValue;
                                toValue += interval;
                            }
                        }
                        else
                        {
                            spreads.Add(string.Format("{0}-{1}", minValue, maxValue), values.Count);
                        }
                    }
                    else if (FieldDataType.IsDateTime(col.DbType))
                    {
                        TimeSpan timeInterval =
                            new TimeSpan(
                                (DateTime.Parse(max.ToString()) - DateTime.Parse(min.ToString())).Ticks /
                                10);
                        DateTime minValue = DateTime.Parse(min.ToString());
                        DateTime maxValue = DateTime.Parse(max.ToString());

                        if (timeInterval.Days >= 1)
                        {
                            DateTime fromValue = minValue;
                            DateTime toValue = fromValue.Add(timeInterval);
                            while (toValue < maxValue)
                            {
                                int count = 0;
                                foreach (object item in values)
                                {
                                    DateTime value = DateTime.Parse(item.ToString());
                                    if (value >= fromValue && value < toValue)
                                    {
                                        count++;
                                    }
                                    if (value > toValue)
                                    {
                                        break;
                                    }
                                }

                                spreads.Add(string.Format("{0}-{1}", fromValue, toValue), count);
                                fromValue = toValue;
                                toValue = toValue.Add(timeInterval);
                            }
                        }
                        else
                        {
                            spreads.Add(string.Format("{0}-{1}", minValue, maxValue), values.Count);
                        }
                    }
                    else if (FieldDataType.IsStringType(col.DbType))
                    {
                        string[] stringRange = new string[]
                                                   {
                                                       "A", "B", "C", "D", "E", "F", "G",
                                                       "H", "I", "J", "K", "L", "M", "N",
                                                       "O", "P", "Q", "R", "S", "T", "U",
                                                       "V", "W", "X", "Y", "Z"
                                                   };
                        foreach (object item in values)
                        {
                            string value = item.ToString();
                            bool found = false;
                            for (int i = 0; i < stringRange.Length; i++)
                            {
                                if (value.ToUpper().StartsWith(stringRange[i]))
                                {
                                    found = true;
                                    Range<IComparable> range = new Range<IComparable>(stringRange[i], stringRange[i]);
                                    if (
                                        spreads.ContainsKey(string.Format("{0}-{1}", range.FromValue, range.ToValue)))
                                    {
                                        int count =
                                            spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)];
                                        spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)] = count +
                                                                                                                 1;
                                    }
                                    else
                                    {
                                        spreads.Add(string.Format("{0}-{1}", range.FromValue, range.ToValue), 1);
                                    }
                                    break;
                                }
                            }
                            if (!found)
                            {
                                Range<IComparable> range = new Range<IComparable>("_", "_");
                                if (spreads.ContainsKey(string.Format("{0}-{1}", range.FromValue, range.ToValue)))
                                {
                                    int count = spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)];
                                    spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)] = count + 1;
                                }
                                else
                                {
                                    spreads.Add(string.Format("{0}-{1}", range.FromValue, range.ToValue), 1);
                                }
                            }
                        }
                    }
                }

                this._FieldDbType = col.DbType;
                this._EvennessIndex = evennessIdx;
                this._ModePcts = modePercentiles;
                this._Spreads = spreads;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Unable to get field value distribution for {0} in table {1}: {2}",
                                                  this._FieldName, this._TableName, ex.ToString()));
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
